﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ExcleToWord.DataModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace ExcleToWord.Util
{
    class ExcelParseUtil
    {
        private IWorkbook m_iWorkBook;
        public List<RecordEntity> _sheetEntity; //一个工作表的信息
        public List<List<RecordEntity>> _workBookEntity; //一个工作表的信息

        /// <summary>
        /// 初始化工作表
        /// </summary>
        /// <param name="path"></param>
        public void InitialWorkbook(string path)
        {
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                if (System.IO.Path.GetExtension(path) == ".xls")// 2003版本
                {
                    m_iWorkBook = new HSSFWorkbook(file);
                }
                else
                {
                    if (System.IO.Path.GetExtension(path) == ".xlsx")
                    {
                        m_iWorkBook = new XSSFWorkbook(file);
                        //System.Windows.Forms.MessageBox.Show("请使用.xls文件");
                    }
                    else
                    {
                        System.Windows.Forms.MessageBox.Show("文件格式错误");
                    }
                }
            }
        }



        /// <summary>
        ///仅输出第一个工作表中的信息
        /// </summary>
        /// <param name="sheetData"></param>
        public void ReadFirstSheet(out List<RecordEntity> sheetData)
        {

            ISheet sheet = m_iWorkBook.GetSheetAt(0);
            _sheetEntity = new List<RecordEntity>();

            if (!ReadSheetDatas(sheet, out _sheetEntity, 1, -1))
            {
                MessageBox.Show("读取第一个表格失败", "失败");
            }
            sheetData = _sheetEntity;
        }


        /// <summary>
        ///仅输出第一个工作表中的信息
        /// </summary>
        /// <param name="sheetData"></param>
        public   void ReadFirstSheetTitleNames(out List<string> titleColumnNameList)
        {

            ISheet sheet = m_iWorkBook.GetSheetAt(0);
            _sheetEntity = new List<RecordEntity>();

            if (!ReadSheetTitle(sheet, out titleColumnNameList))
            {
                MessageBox.Show("读取第一个表格标题失败", "失败");
            }
        }

        /// <summary>
        /// 读取工作簿Sheet中数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="sheetData"></param>
        /// <param name="bodyStartRowIdx">解析数据的起始行</param>
        /// <param name="removeLastNRow">最后N行不解析</param>
        /// <returns></returns>
        public bool ReadSheetDatas(ISheet sheet, out List<RecordEntity> sheetData, int bodyStartRowIdx = 1, int rowLastIdx = -1)  //Dictionary<> 
        {
            //0. 预处理
            int titleRowIdx = 0; //默认标题只在第一行
            int bodyStartColumIdx = 0; //开始解析数据的列

            string sheetName = sheet.SheetName.Trim(); //工作簿的名称

            // 返回数据对象集合
            sheetData = new List<RecordEntity>();
            List<string> titleColumnNameList = new List<string>(); //标题行的列名集合


            //判断行数是否满足
            if (rowLastIdx == -1)
            {
                rowLastIdx = sheet.LastRowNum;
            }
            if (rowLastIdx < bodyStartRowIdx)
            {
                MessageBox.Show("表格中数据为空或者传入参数错误" + sheetName, "错误");
                return false;
            }

            //1.循环标题行，获得标题行数据
            //titleRowIdx = 0;
            //从第一列开始
            for (int j = bodyStartColumIdx; j < sheet.GetRow(titleRowIdx).LastCellNum; j++)
            {
                string strCellValue = sheet.GetRow(titleRowIdx).GetCell(j).ToString();
                RemoveCellSpace(ref strCellValue);//去除空格
                titleColumnNameList.Add(strCellValue);
            }

            //2.获得“主体”数据

            // 循环行数据
            for (int rowIdx = bodyStartRowIdx; rowIdx <= rowLastIdx; rowIdx++)  // LastRowNum行号码,也是索引,从0开始
            {
                IRow dataRow = sheet.GetRow(rowIdx);  //获得0起始的行数,如果该行未定义,则返回空
                System.Diagnostics.Debug.Assert(dataRow != null); //如果有空行的话,使用列的计数器可能存在问题

                if (dataRow != null) //屏蔽空行                                    
                {
                    //2.1 以标题行的信息初始化，每一行的“名称”数据
                    RecordEntity recordEnt = new RecordEntity();
                    recordEnt.InitialKeys(titleColumnNameList);

                    // 循环列数据
                    for (int columIdx = bodyStartColumIdx, i = 0; columIdx < dataRow.LastCellNum; columIdx++, i++)  //dataRow.LastCellNum
                    {
                        ICell cell = dataRow.GetCell(columIdx);
                        string strCellValue = cell.ToString();
                        RemoveCellSpace(ref strCellValue);  // 去除空格

                        if (i < recordEnt.RecordList.Count)
                        {
                            string OldKey = recordEnt.RecordList[i].Key;
                            recordEnt.RecordList[i] = new KeyValuePair<string, string>(OldKey, strCellValue);
                        }
                    }
                    sheetData.Add(recordEnt);  //必须groupData填充完数据后,才能增加到sheetData中

                }
            }
            return true; //Todo数据有效性待验证
        }


        /// <summary>
        /// 读取工作簿Sheet中数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="sheetData"></param>
        /// <param name="bodyStartRowIdx">解析数据的起始行</param>
        /// <param name="removeLastNRow">最后N行不解析</param>
        /// <returns></returns>
        private bool ReadSheetTitle(ISheet sheet, out List<string> titleColumnNameList)  //Dictionary<> 
        {
            //0. 预处理
            int titleRowIdx = 0; //默认标题只在第一行
            int bodyStartColumIdx = 0; //开始解析数据的列
            string sheetName = sheet.SheetName.Trim(); //工作簿的名称
            titleColumnNameList = new List<string>(); //标题行的列名集合


            //1.循环标题行，获得标题行数据

            //从第一列开始
            for (int j = bodyStartColumIdx; j < sheet.GetRow(titleRowIdx).LastCellNum; j++)
            {
                string strCellValue = sheet.GetRow(titleRowIdx).GetCell(j).ToString();
                RemoveCellSpace(ref strCellValue);//去除空格
                titleColumnNameList.Add(strCellValue);
            }

            if (titleColumnNameList.Count != 0)
            {
                return true;
            }

            return false; //Todo数据有效性待验证
        }

        // 去除空值
        private void RemoveCellSpace(ref string cellValue)
        {
            cellValue = TruncateString(cellValue, new char[] { ' ' }, new char[] { '　' }); //两种类型的"空格"
        }

        // 对字符串做空格剔除处理
        private string TruncateString(string originalWord, char[] spiltWord1, char[] spiltWord2)
        {
            var result = "";
            string[] valueReplaceDbcCase = originalWord.Split(spiltWord1);

            if (valueReplaceDbcCase.Count() > 1)
            {
                for (int i = 0; i < valueReplaceDbcCase.Count(); i++)
                {
                    if (valueReplaceDbcCase[i] != "" && valueReplaceDbcCase[i] != " " &&
                        valueReplaceDbcCase[i] != "　")
                    {
                        result += TruncateString(valueReplaceDbcCase[i], spiltWord2, new char[0]);
                    }
                }
            }
            else
            {
                if (spiltWord2.Any())
                {
                    result = TruncateString(originalWord, spiltWord2, new char[0]);
                }
                else
                {
                    result = originalWord;
                }
            }
            return result;
        }
    }
}
